<!---
*****************************************************************************************************
*Author: Jon Wallen                                                                                 *
*www.cfshopkart.com                                                                                 *
*                                                                                                   *
*                                                                                                   *
*Performs an insert or an update on a db table.  Form fields that match data columns are updated or *
*inserted.  I developed this CFC because I was tired of writing long insert and update queries.     *
*All you need to do is make sure you type the name of the form field exactly as you have named      *
*the column  If you do not want to update the field just leave the form field out or make sure it's *
*named something different than the column.                                                         *
*If you want to add something to your data, then all you have to do is insert a column in your db   *
*then add a form field; no need to edit the query statements!                                       *
*                                                                                                   *
*Version:  1.3                                                                                      *
*****************************************************************************************************

Revision History:

1.1 - Add check for blank field.  Now sets field to empty if form field is blank instead of ignoring it.
1.2 - Fixed inserts bugs.
1.2 - On insert, now returns last id of record (mysql only)

Valid methods:  updatedata 
				insertdata

Sample use:

<cfinvoke component="dbqueries" method="updatedata" returnvariable="outputmsg">
	<cfinvokeargument name="tablename" value="mytable">
	<cfinvokeargument name="dsn" value="myDSN">
	<cfinvokeargument name="wherestring" value="WHERE id = '#form.menuid#'">
</cfinvoke>

tablename (required):  The name of the table in your db you want to insert into or update.
dsn (required): The datasource name you setup in your cf administrator.
dbuser (optional): The username for the datasource.
dbpass (optional): The CustPassword for the datasource.
wherestring (for updates): The full sql WHERE string if you want to udpate a specific record (i.e. WHERE id = 1).
--->
<cfcomponent>
	<!---****INSERTS*****---->
	<cffunction name="insertdata" returntype="string" description="Performs an insert on a db table">
	<cfargument name = "tablename" type="string" required="yes" hint="Enter the name of the table you want to insert into">
	<cfargument name = "dsn" type="string" required="no" default="#request.dsn#" hint="Use this to override the default request.dsn value.">

	<cfif isdefined('dsn')>
    	<cfset request.dsn = dsn>
    </cfif>

	<!---Get the columns in the called table--->
	<cfquery name = "qryCols" datasource="#dsn#">
	SELECT * FROM #tablename#
	</cfquery>
	  
	<cfset colsmeta=getMetaData(qryCols)>
	<cfset cList = qryCols.columnlist>

	<cfset icount = 0>
		
	<!---build query--->
    <cftransaction>
    <cfquery name = "qInsertData" datasource="#dsn#">
	INSERT INTO #tablename#
	(<cfloop from = "1" to="#listlen(cList)#" index="cCount">
		<cfset thiscol = colsmeta[cCount].Name>
		<cfset thiscol = lcase(thiscol)>
		<cfset thisvar = "form.#thiscol#">
		<!---If the corresponding form field is found then insert into db (this portion builds the list of columns)--->
		<cfif isdefined(thisvar)>
		<cfset icount = icount + 1>
		<cfset thisval = '#evaluate(thisvar)#'>
			<cfif icount IS 1>#thiscol#<cfelse>,#thiscol#</cfif>
		</cfif>
	</cfloop>)
	VALUES
	(<cfset icount = 0><cfloop from = "1" to="#listlen(cList)#" index="vCount">
	<!---This section builds the values to insert into each corresponding column (forms must be named after their db column)--->
		<cfset thiscol = colsmeta[vCount].Name>
		<cfset thiscol = lcase(thiscol)>
		<cfset thisvar = "form.#thiscol#">
		<cfif isdefined(thisvar)>
		<cfset icount = icount + 1>
		<cfset thisval = '#evaluate(thisvar)#'>
		<cfset coltype =  colsmeta[vCount].TypeName>
			<!---Determine if we are at the start of the list, if not then put in a leading comma--->
			<cfif icount IS 1>
				<!---Determine if the value for this field needs quotes (string) or not (numeric/datetime)--->
				<cfif coltype IS 'VARCHAR' OR coltype IS 'LONGTEXT' OR coltype IS 'TEXT' OR coltype IS 'MEDIUMTEXT'
				OR coltype IS 'BLOB' OR coltype IS 'MEMO'>
				'#thisval#'<cfelse><cfif len(trim(thisval)) IS 0>0<cfelse>#thisval#</cfif>
				</cfif>
				<!---Otherwise it's the end of the statement so don't put a trailing comma--->
			<cfelse>
				<!---Same as above: determine column type--->
				<cfif coltype IS 'VARCHAR' OR coltype IS 'LONGTEXT' OR coltype IS 'TEXT' OR coltype IS 'MEDIUMTEXT'
				OR coltype IS 'BLOB'>
					,'#thisval#'<cfelse><cfif len(trim(thisval)) IS 0>0<cfelse>,#thisval#</cfif>
				</cfif>
			</cfif>
		</cfif>
	</cfloop>)
	</cfquery>

    <cfquery name = "qID" datasource="#request.dsn#">
    SELECT LAST_INSERT_ID() AS newid
    </cfquery>
    </cftransaction> 

	<cfreturn qID.newid>
    </cffunction>
	
	<!---****UPDATES*****---->
	<cffunction name="updatedata" returntype="string" description="Performs an update on a db table">
	<!---Table name must be defined--->
	<cfargument name = "tablename" type="string" required="yes" hint="Enter the name of the table you want to update">
	<cfargument name = "wherestring" type="string" required="no" default="" hint="Full WHERE statement for update query">
	<cfargument name = "dsn" type="string" required="no" default="#request.dsn#" hint="Enter the dsn to perform this action against">

	<cfif isdefined('dsn')>
    	<cfset request.dsn = dsn>
    </cfif>
	
	<!---Get the columns in the called table--->
	<cfquery name = "qryCols"datasource="#dsn#">
	SELECT * FROM #tablename#
	</cfquery>
	
	<cfset colsmeta=getMetaData(qryCols)>
	
	<cfset cList = qryCols.columnlist>
	<cfset icount = 0>
	
	<cfquery name = "qUpdatedata" datasource="#dsn#">
	UPDATE #tablename#
	SET <cfloop from = "1" to="#listlen(cList)#" index="cCount">
			<cfset thiscol = colsmeta[cCount].Name>
			<cfset thiscol = lcase(thiscol)>
			<cfset thisvar = "form.#thiscol#">
			<cfif isdefined(thisvar)>
				<cfset thisval = '#evaluate(thisvar)#'>
					<cfset coltype = colsmeta[cCount].TypeName>
					<!---If it's a type that is a string then include quotes around the value--->
					<cfif coltype IS 'VARCHAR' OR coltype IS 'LONGTEXT' OR coltype IS 'TEXT' OR coltype IS 'MEDIUMTEXT'
					OR coltype IS 'BLOB' OR coltype IS 'MEMO'>
						<cfset icount = icount + 1>
						<!---If it's the first one then do not include a comma--->		
						<cfif iCount IS 1>
							#thiscol#='#thisval#'
						<!---If it's after the first one then put a leading comma in--->
						<cfelse>
						<cfif len(thisval) GT 0>
                        	,#thiscol#='#thisval#'
                        <cfelse>
                        	,#thiscol#=''
                        </cfif><!---End if the length is GT 0--->
						</cfif>
					<!---If not, then do not include the quotes--->
					<cfelse>
						<cfif isnumeric(thisval)>
							<cfset icount = icount + 1>
							<cfif iCount IS 1>
								#thiscol#=#thisval#
							<cfelse>
							<cfif len(thisval) GT 0>
                            	,#thiscol#=#thisval#
							<cfelse>
                                ,#thiscol# = 0
                            </cfif>                            
							<!---End if the lenght is GT 0--->
							</cfif>
					</cfif><!---end if the value is a number--->
				</cfif><!---End if it's a string type field--->				
			</cfif><!---End if the form field is present--->
		</cfloop>
	<cfif len(trim(wherestring)) GT 0>#replacenocase(wherestring, "''", "'", "ALL")#</cfif>
	</cfquery>
	</cffunction>
</cfcomponent>